/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.uit.ecommerce.access.mapper;

import com.uit.ecommerce.dto.AreaDTO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author ngloc_it
 */
public class AreaMapper extends MapperDB {

    public AreaMapper() throws Exception{
        super();
    }

    /**
     * Create an AreaDTO object from result set
     * @param area to hold return value
     * @param rs to get source  to create object
     * @throws Exception
     */
    private void InitAreaDTOFromRs(AreaDTO area, ResultSet rs) throws Exception{
        if((rs != null) && (area != null)){
            area.setCountryId(rs.getString("MaNuoc"));
            area.setId(rs.getString("MaKhuVuc"));
            area.setName(rs.getString("KhuVuc"));
        }
    }

    /**
     * Get all area in database
     * @return
     * @throws Exception
     */
    public ArrayList<AreaDTO> GetAllArea() throws Exception{
        ArrayList<AreaDTO> listResult = new ArrayList<AreaDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ecormerce.khuvuc");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            while((rs != null) && (rs.next())){
                AreaDTO areaTemp = new AreaDTO();
                InitAreaDTOFromRs(areaTemp, rs);
                listResult.add(areaTemp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }

    /**
     * Get an AreaDTO object by it's code
     * @param id code of Area
     * @return AreaDTO found
     * @throws Exception
     */
    public AreaDTO GetAreaById(String id) throws Exception{
        AreaDTO result = new AreaDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ecormerce.khuvuc ");
            sql.append("where MaKhuVuc = '").append(id);
            sql.append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            if((rs != null) && (rs.next())){
                InitAreaDTOFromRs(result, rs);
            }
        }catch(Exception ex){
            throw ex;
        }

        return result;
    }

    /**
     * Get an Area by it's Name
     * @param Name Name for search
     * @return AreaDTO found, null if not found
     * @throws Exception
     */
     public AreaDTO GetAreaByName(String Name) throws Exception{
        AreaDTO result = new AreaDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ecormerce.khuvuc ");
            sql.append("where KhuVuc = '").append(Name);
            sql.append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            if((rs != null) && (rs.next())){
                InitAreaDTOFromRs(result, rs);
            }
        }catch(Exception ex){
            throw ex;
        }

        return result;
    }

    /**
     * Check if an Id of new Area existed in database
     * @param Id Id for check
     * @return True if the Id existed in database, false if not
     * @throws Exception
     */
    public boolean IsIdExisted(String Id) throws Exception{
        boolean result = false;
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ecormerce.khuvuc ");
            sql.append("where MaKhuVuc = '").append(Id);
            sql.append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            if((rs != null) && (rs.next())){
                result = true;
            }
        }catch(Exception ex){
            throw ex;
        }
        return result;
    }

    /**
     *
     * @param countryId
     * @return
     * @throws Exception
     */
    public ArrayList<AreaDTO> GetAreaByCountryCode(String countryId) throws Exception{
        ArrayList<AreaDTO> listResult = new ArrayList<AreaDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ecormerce.khuvuc ");
            sql.append("where MaNuoc = '").append(countryId);
            sql.append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            while((rs != null) && (rs.next())){
                AreaDTO areaTemp = new AreaDTO();
                InitAreaDTOFromRs(areaTemp, rs);
                listResult.add(areaTemp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }
    public ArrayList<AreaDTO> GetAreaByCountryName(String country) throws Exception{
        ArrayList<AreaDTO> listResult = new ArrayList<AreaDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select MaKhuVuc, KhuVuc.MaNuoc, KhuVuc from ecormerce.khuvuc,ecormerce.datnuoc where khuvuc.MaNuoc=datnuoc.MaNuoc and TenNuoc='"+country+"' ");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            while((rs != null) && (rs.next())){
                AreaDTO areaTemp = new AreaDTO();
                InitAreaDTOFromRs(areaTemp, rs);
                listResult.add(areaTemp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }

    /**
     * Insert an Area into database
     * @param area area to insert
     * @return true if successful, and fale if fail
     * @throws Exception
     */
    public boolean InsertArea(AreaDTO area) throws Exception{
        boolean result = false;
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Insert into ecormerce.khuvuc values( ");
            sql.append("'").append(area.getName()).append("', ");
            sql.append("'").append(area.getId()).append("', ");
            sql.append("'").append(area.getCountryId()).append("')");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            result = stmt.execute();
        }catch(Exception ex){
            throw ex;
        }
        return result;
    }

    /**
     * Delete an Area in database
     * @param id Id of Area need to delete
     * @throws Exception
     */
    public void DeleteArea(String id) throws Exception{
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("Delete from ecormerce.khuvuc Where MaKhuVuc = '");
            sql.append(id).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
        }catch(Exception ex){
            throw ex;
        }
    }


}